﻿/******************************************************   
创建者: 王东杰
创建时间: 2017-04-12
修改:获取数据由goods.goods_PL_Item_SkcPcs迁移到goods.goods_PL_Item_SkcPcsDetail,
保存数据由goods.goods_PL_Item_SkcForPC迁移到goods.goods_PL_Item_SkcForPCDetail
修改时间: 2017-05-15
修改:替换查询店铺控件
修改时间: 2017-05-25
修改:价格带只保留一位小数,eg.100.0,199.9
修改时间: 2017-06-16
功能: 小品类到价格带SKC数量
修改时间: 2017-07-19
功能: 增加多主体
 ******************************************************/

using System;
using System.Collections;
using System.Collections.Generic;
using System.ComponentModel;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using com.nm.ApiServer;
using com.nm.ApiServer.Attributes;
using com.nm.Attributes;
using george.Data;
using NHibernateVertica;
using PlanAPI.model;
using System.Data;
using System.Diagnostics;
using com.nm.library.Helper;
using com.nm.Helper;
using george.Helper;
using com.nm.library;

namespace PlanAPI
{
    [Description("小品类到价格带SKC数量")]
    public class PLItemPriceZoneSKCHandler : BaseHandler
    {
        public VerticaDataHelper Helper { get; set; } = new VerticaDataHelper();
        Stopwatch stopWatchTotal = new Stopwatch();

        #region 获取数据库中的数据,不计算

        [MustLogin]
        [Description("小品类到价格带SKC数量列表")]
        [ReturnType(typeof(PaperData<IGrouping<string, PLItemPriceZoneSkcModel.ListModel>>))]
        public ApiResult GetList(
            [ParamDescription("商品区域")] string ParentAreaID = "",
            [ParamDescription("类别")]string KindID = "",
            [ParamDescription("系列")]string SeriesID = "",
            [ParamDescription("商品层")]string MaterialProperty = "",
            [ParamDescription("品类")]string ModelID = "",
            [ParamDescription("小品类")]string ItemID = "",
            [ParamDescription("款")]string DataFlag = "",
            [ParamDescription("店铺shopid")]string ShopIDs = "",
            [ParamDescription("页码")] int Page = 1,
            [ParamDescription("页数")] int Size = 10)
        {
            var set = new PLPlanYMSetHandler().ReturnPLPlanYMSet();
            if (set == null) return Error("没有要修改企划的月份");
            var count = ReturnCount(false, set.Yearv.Value, set.Monthv.Value, ParentAreaID, KindID, SeriesID, MaterialProperty, ModelID, ItemID, DataFlag, ShopIDs.GetString());
            var dt = ReturnList(false, set.Yearv.Value, set.Monthv.Value, ParentAreaID, KindID, SeriesID, MaterialProperty, ModelID, ItemID, DataFlag, ShopIDs.GetString(), Page, Size);
            var result = PaperData<IGrouping<string, PLItemPriceZoneSkcModel.ListModel>>.Create(count, Size, Page);
            var list = ModelConvertHelper<PLItemPriceZoneSkcModel.ListModel>.ConvertToModel(dt);

            result.Items =
                                    list.GroupBy(
                                        d =>
                                            d.Yearv + "_" + d.Monthv + "_" + d.ParentAreaID + "_" + d.ShopID + "_" +
                                            d.KindID + "_" + d.MaterialProperty + "_" + d.ModelID + "_" + d.SeriesID + "_" +
                                            d.ItemID + "_" + d.DataFlag + "_" + d.CkSKCQty + "_" + d.Weekv
                                            ).ToList();
            return Success(result);
        }

        #endregion 获取数据库中的数据,不计算

        #region 获取数据库中的数据,并进行计算

        [MustLogin]
        [Description("小品类到价格带SKC数量列表")]
        [ReturnType(typeof(PaperData<IGrouping<string, PLItemPriceZoneSkcModel.ListModel>>))]
        public ApiResult GetComputedList(
            [ParamDescription("商品区域")] string ParentAreaID = "",
            [ParamDescription("类别")]string KindID = "",
            [ParamDescription("系列")]string SeriesID = "",
            [ParamDescription("商品层")]string MaterialProperty = "",
            [ParamDescription("品类")]string ModelID = "",
            [ParamDescription("小品类")]string ItemID = "",
            [ParamDescription("款")]string DataFlag = "",
            [ParamDescription("店铺shopid")]string ShopIDs = "",
            [ParamDescription("页码")] int Page = 1,
            [ParamDescription("页数")] int Size = 10)
        {
            var set = new PLPlanYMSetHandler().ReturnPLPlanYMSet();
            if (set == null) return Error("没有要修改企划的月份");
            var count = ReturnCount(true, set.Yearv.Value, set.Monthv.Value, ParentAreaID, KindID, SeriesID, MaterialProperty, ModelID, ItemID, DataFlag, ShopIDs.GetString());
            var dt = ReturnList(true, set.Yearv.Value, set.Monthv.Value, ParentAreaID, KindID, SeriesID, MaterialProperty, ModelID, ItemID, DataFlag, ShopIDs.GetString(), Page, Size);
            //删除数据库中的数据
            var cmdList = DeleteComputedList2DB(set.Yearv.Value, set.Monthv.Value, ParentAreaID, KindID, SeriesID, MaterialProperty, ModelID, ItemID, DataFlag, ShopIDs.GetString());
            Helper.ExecuteSqlTran(cmdList);
            if (dt?.Rows.Count > 0)
            {

                var result = PaperData<IGrouping<string, PLItemPriceZoneSkcModel.ListModel>>.Create(count, count, Page);
                var list = ModelConvertHelper<PLItemPriceZoneSkcModel.ListModel>.ConvertToModel(dt);

                result.Items =
                              list.GroupBy(
                                            d =>
                                                d.Yearv + "_" + d.Monthv + "_" + d.ParentAreaID + "_" + d.ShopID + "_" +
                                                d.KindID + "_" + d.MaterialProperty + "_" + d.ModelID + "_" + d.SeriesID + "_" +
                                                 d.ItemID + "_" + d.DataFlag + "_" + d.PriceColorName
                                                ).ToList();
                var newList = ComputeAll(result);
                List<CommandInfo> cmdEmpty = new List<CommandInfo>();
                ExecuteSkcCalc(cmdEmpty, newList);
            }
            //插入推品的数据到数据库
            InsertTuiPin2DB(set.Yearv.Value, set.Monthv.Value, ParentAreaID, KindID, SeriesID, MaterialProperty, ModelID, ItemID, DataFlag, ShopIDs.GetString(), Page, Size);
            var resultList = GetList(ParentAreaID, KindID, SeriesID, MaterialProperty, ModelID, ItemID, DataFlag, ShopIDs, Page, Size);
            return resultList;
        }

        #endregion 获取数据库中的数据,并进行计算

        #region 返回数据列表

        /// <summary>
        /// <param name="ParentAreaID"></param>
        /// <param name="KindID"></param>
        /// <param name="SeriesID"></param>
        /// <param name="MaterialProperty"></param>
        /// <param name="ModelID"></param>
        /// <param name="Keys"></param>        /// 返回小品类到价格带SKC数量
        /// </summary>
        /// <param name="Page"></param>
        /// <param name="Size"></param>
        /// <returns></returns>
        public DataTable ReturnList(bool IsCompute, long Yearv, long Monthv,
            string ParentAreaID, string KindID,
            string SeriesID, string MaterialProperty,
            string ModelID, string ItemID, string DataFlag,
            string ShopIDs, int Page, int Size)
        {
            try
            {

                var strSql = string.Empty;
                if (!IsCompute)
                {
                    #region 查询时返回数据列表


                    //如果只是展示列表,就查询出所有系列的数据,包含推品

                    strSql = $@"
 
SELECT
			sp.yearv,
			sp.monthv,
			sp.parentareaid,
			shop.shopname AS ShopName,
			kind.kindname AS KindName,
			series.seriesname AS SeriesName,
			sp.materialproperty,
			model.modelname AS ModelName,
			sp.weekv,
			item.itemname AS ItemName,
			sp.ckskcqty,
			cast(
				sp.PriceColorName as decimal(
					8,
					1
				)
			) as PriceColorName,
			sp.CkPtStandAmountRate,
			sp.seriesid,
			sp.itemid,
			shop.shopcode AS ShopCode,
			sp.shopid,
			sp.kindid,
			sp.modelid,
            sp.DeepQty as DeepQty,
			sp.totalqty,
			sp.calcqty,
			sp.roundqty,
			sp.diffvalue,
			sp.addqty,
			sp.pricecolorskcqty,
            sp.CheckState,
            sp.DataFlag
		FROM
			ngoods.ngoods_pl_item_skcforpcDetail sp
		LEFT JOIN ndw.ndw_shop shop ON   sp.ExtrSource = shop.ExtrSource and sp.CompanyID=shop.CompanyID and
			sp.shopid = shop.shopid
		LEFT JOIN ndw.ndw_global_dictionary dict ON   sp.ExtrSource = dict.ExtrSource and sp.CompanyID=dict.CompanyID and
			sp.parentareaid = dict.dictvalue
		LEFT JOIN nods.nods_erp_sd_mat_kind kind ON  sp.ExtrSource = kind.ExtrSource and
			sp.kindid = kind.kindid
		LEFT JOIN nods.nods_erp_sd_mat_model model ON  sp.ExtrSource = model.ExtrSource and
			sp.modelid = model.modelid
		LEFT JOIN nods.nods_erp_sd_mat_series series ON sp.ExtrSource = series.ExtrSource and
			sp.seriesid = series.seriesid
		LEFT JOIN nods.nods_erp_sd_mat_item item ON  sp.ExtrSource = item.ExtrSource and
			sp.itemid = item.itemid
		WHERE
    sp.ExtrSource = '{UserInfo.UserExtrSource}' and sp.CompanyID= '{UserInfo.UserCompanyId}'  and
		sp.priceorcolor = 0 and
			sp.ckskcqty > 0 and
			
			
			 sp.Yearv={Yearv} and sp.Monthv={Monthv}  

{(string.IsNullOrWhiteSpace(ParentAreaID) ? "" : $" and sp.ParentAreaID = '{ParentAreaID}' ")}
{ (string.IsNullOrWhiteSpace(KindID) ? "" : $" and sp.KindID='{KindID}' ")}
                    { (string.IsNullOrWhiteSpace(SeriesID) ? "" : $" and sp.SeriesID='{SeriesID}' ")}
                    { (string.IsNullOrWhiteSpace(MaterialProperty) ? "" : $" and sp.MaterialProperty='{MaterialProperty}' ")}
                    { (string.IsNullOrWhiteSpace(ModelID) ? "" : $" and sp.ModelID='{ModelID}' ")}
                    { (string.IsNullOrWhiteSpace(ItemID) ? "" : $" and sp.ItemID='{ItemID}' ")}
                    {(string.IsNullOrWhiteSpace(DataFlag) ? "" : $" and sp.DataFlag='{DataFlag}' ")}

                    {
                        (string.IsNullOrWhiteSpace(ShopIDs)
                                               ? ""
                                               : $" and  shop.shopid in {ShopIDs}  ")}


                    order by

    Yearv,
	Monthv,
	ParentAreaID,
	ShopID,
	KindID,
	MaterialProperty,
	ModelID,
	SeriesID,
	ItemName,
    DataFlag,
	Weekv,
    PriceColorName desc

LIMIT {Size} offset {(Page - 1.0) * Size}	
";


                    #endregion 查询时返回数据列表
                }
                else
                {
                    #region 计算时返回的数据列表
                    strSql = $@"

SELECT
	p.Yearv,
	p.Monthv,
	p.ParentAreaID,
	p.ShopName as ShopName,
	p.KindName as KindName,
	p.SeriesName as SeriesName,
	p.MaterialProperty,
	p.ModelName as ModelName,
	p.Weekv,
	p.ItemName as ItemName,
	p.CkSKCQty,
    min(sp.TotalQty) as TotalQty,
	p.PriceColorName,
	p.CkPtStandAmountRate,
	p.SeriesID,
	p.ItemID,
	p.ShopCode as ShopCode,
	p.ShopID,
	p.KindID,
	p.ModelID,
    p.DataFlag,
    min( p.DeepQty) as DeepQty,
	min( sp.CalcQty ) as CalcQty,
	min( sp.RoundQty ) as RoundQty,
	min( sp.DiffValue ) as DiffValue,
	min( sp.AddQty ) as AddQty,
    min( sp.PriceColorSKCQty ) as PriceColorSKCQty
from
	(
		select
			s.Yearv,
			s.Monthv,
			s.ParentAreaID,
			shop.ShopName as ShopName,
			kind.KindName as KindName,
			series.SeriesName as SeriesName,
			s.MaterialProperty,
			model.ModelName as ModelName,
			s.Weekv,
			item.ItemName as ItemName,
			s.CkSKCQty,
			cast(
				p.PriceName as decimal(
					8,
					1
				)
			) as PriceColorName,
			p.CkPtStandAmountRate,
			s.SeriesID,
			s.ItemID,
			shop.ShopCode as ShopCode,
			s.ShopID,
			s.KindID,
			s.ModelID,
            s.CKMoveDeepQty as DeepQty,
            s.DataFlag
		from
						ngoods.ngoods_PL_Item_SkcPcsDetail s left join ngoods.ngoods_PL_Item_PricePeriod p on
			s.ParentAreaID = p.ParentAreaID  and p.ExtrSource = s.ExtrSource and p.CompanyID = s.CompanyID
			and s.Yearv = p.Yearv
			and s.Monthv = p.Monthv
			and s.ParentAreaID = p.ParentAreaID
			and s.KindID = p.KindID
			and s.SeriesID = p.SeriesID
			and s.MaterialProperty = p.MaterialProperty
			and s.ModelID = p.ModelID
			and s.ItemID = p.ItemID
			and p.CkPtStandAmountRate > 0 left join ndw.ndw_Shop shop on  s.ExtrSource = shop.ExtrSource and s.CompanyID=shop.CompanyID and
			s.ShopID = shop.ShopID left join ndw.ndw_global_dictionary dict on s.ExtrSource = dict.ExtrSource and s.CompanyID=dict.CompanyID and 
			s.ParentAreaID = dict.dictValue left join nods.nods_erp_SD_Mat_Kind kind on s.ExtrSource = kind.ExtrSource and 
			s.KindID = kind.KindID left join nods.nods_erp_SD_Mat_Model model on s.ExtrSource = model.ExtrSource and 
			s.ModelID = model.ModelID left join nods.nods_erp_SD_Mat_Series series on  s.ExtrSource = series.ExtrSource and 
			s.SeriesID = series.SeriesID left join nods.nods_erp_SD_Mat_Item item on s.ExtrSource = item.ExtrSource and 
			s.ItemID = item.ItemID
		WHERE
                        s.ExtrSource = '{UserInfo.UserExtrSource}' and s.CompanyID= '{UserInfo.UserCompanyId}'  and
			   s.CkSKCQty > 0 and
						  s.Yearv={Yearv} and s.Monthv={Monthv}  

{(string.IsNullOrWhiteSpace(ParentAreaID) ? "" : $" and s.ParentAreaID='{ParentAreaID}' ")}
{(string.IsNullOrWhiteSpace(KindID) ? "" : $" and s.KindID='{KindID}' ")}
{(string.IsNullOrWhiteSpace(SeriesID) ? "" : $" and s.SeriesID='{SeriesID}' ")}
{(string.IsNullOrWhiteSpace(MaterialProperty) ? "" : $" and s.MaterialProperty='{MaterialProperty}' ")}
{(string.IsNullOrWhiteSpace(ModelID) ? "" : $" and s.ModelID='{ModelID}' ")}
{(string.IsNullOrWhiteSpace(ItemID) ? "" : $" and s.ItemID='{ItemID}' ")}
{(string.IsNullOrWhiteSpace(DataFlag) ? "" : $" and s.DataFlag='{DataFlag}' ")}
{(string.IsNullOrWhiteSpace(ShopIDs)
                        ? ""
                        : $" and  shop.shopid in {ShopIDs}  ")} 
		
			 
	) as p  left join ngoods.ngoods_PL_Item_SkcForPCDetail sp on
    sp.ExtrSource = '{UserInfo.UserExtrSource}' and sp.CompanyID= '{UserInfo.UserCompanyId}'  and
	p.ParentAreaID = sp.ParentAreaID
	and p.Yearv = sp.Yearv
	and p.Monthv = sp.Monthv
	and p.ParentAreaID = sp.ParentAreaID
	and p.KindID = sp.KindID
	and p.SeriesID = sp.SeriesID
	and p.MaterialProperty = sp.MaterialProperty
	and p.ModelID = sp.ModelID
	and p.ItemID = sp.ItemID
	and p.DataFlag = sp.DataFlag
	and p.PriceColorName = to_number(sp.PriceColorName)
	and p.Weekv = sp.Weekv
    and p.ShopId = sp.ShopId
	and sp.PriceOrColor = 0

group by
	p.Yearv,
	p.Monthv,
	p.ParentAreaID,
	p.ShopName,
	p.KindName,
	p.SeriesName,
	p.MaterialProperty,
	p.ModelName,
	p.ItemName,
	p.Weekv,
	p.CkSKCQty,
    p.PriceColorName,
	p.CkPtStandAmountRate,
	p.SeriesID,
	p.ItemID,
    p.DataFlag,
	p.ShopCode,
	p.ShopID,
	p.KindID,
	p.ModelID
order by
	p.Yearv,
	p.Monthv,
	p.ParentAreaID,
	p.ShopID,
	p.KindID,
	p.MaterialProperty,
	p.ModelID,
	p.SeriesID,
	p.ItemName,
    p.DataFlag,
	p.Weekv,
	p.CkSKCQty,
	PriceColorName

";
                    #endregion 计算时返回的数据列表
                }
                var cmd = Helper.GetSqlStringCommand(strSql);
                return Helper.ExecuteDataTable(cmd);
            }
            catch (Exception ex)
            {
                throw (ex);
            }
        }

        #endregion 返回数据列表

        #region 返回行数
        /// <summary>
        /// 返回小品类到价格带SKC数量总条数
        /// </summary>
        /// <param name="IsCompute"></param>
        /// <param name="Yearv"></param>
        /// <param name="Monthv"></param>
        /// <param name="ParentAreaID"></param>
        /// <param name="KindID"></param>
        /// <param name="SeriesID"></param>
        /// <param name="MaterialProperty"></param>
        /// <param name="ModelID"></param>
        /// <param name="ItemID"></param>
        /// <param name="DataFlag"></param>
        /// <param name="Keys"></param>
        /// <returns></returns>
        public int ReturnCount(bool IsCompute, long Yearv, long Monthv, string ParentAreaID,
            string KindID, string SeriesID, string MaterialProperty,
            string ModelID, string ItemID, string DataFlag, string ShopIDs)
        {
            try
            {
                var countSql = string.Empty;
                if (IsCompute)
                {
                    #region 计算时获取行数

                    countSql = $@"

select count(1) as count from (
SELECT
	p.Yearv,
	p.Monthv,
	p.ParentAreaID,
	p.ShopName as ShopName,
	p.KindName as KindName,
	p.SeriesName as SeriesName,
	p.MaterialProperty,
	p.ModelName as ModelName,
	p.Weekv,
	p.ItemName as ItemName,
	p.CkSKCQty,
	min( sp.TotalQty ) as TotalQty,
	p.PriceColorName,
	p.CkPtStandAmountRate,
	p.SeriesID,
	p.ItemID,
	p.ShopCode as ShopCode,
	p.ShopID,
	p.KindID,
	p.ModelID,
	min( p.DeepQty ) as DeepQty,
	min( sp.CalcQty ) as CalcQty,
	min( sp.RoundQty ) as RoundQty,
	min( sp.DiffValue ) as DiffValue,
	min( sp.AddQty ) as AddQty,
	min( sp.PriceColorSKCQty ) as PriceColorSKCQty,
    p.dataflag
from
	(
		select
			s.Yearv,
			s.Monthv,
			s.ParentAreaID,
			shop.ShopName as ShopName,
			kind.KindName as KindName,
			series.SeriesName as SeriesName,
			s.MaterialProperty,
			model.ModelName as ModelName,
			s.Weekv,
			item.ItemName as ItemName,
			s.CkSKCQty,
			cast(
				p.PriceName as decimal(
					8,
					1
				)
			) as PriceColorName,
			p.CkPtStandAmountRate,
			s.SeriesID,
			s.ItemID,
			shop.ShopCode as ShopCode,
			s.ShopID,
			s.KindID,
			s.ModelID,
			s.CKMoveDeepQty as DeepQty,
            s.dataflag
		from
						ngoods.ngoods_PL_Item_SkcPcsDetail s left join ngoods.ngoods_PL_Item_PricePeriod p on
			s.ParentAreaID = p.ParentAreaID  and p.ExtrSource = s.ExtrSource and p.CompanyID = s.CompanyID
			and s.Yearv = p.Yearv
			and s.Monthv = p.Monthv
			and s.ParentAreaID = p.ParentAreaID
			and s.KindID = p.KindID
			and s.SeriesID = p.SeriesID
			and s.MaterialProperty = p.MaterialProperty
			and s.ModelID = p.ModelID
			and s.ItemID = p.ItemID
			and p.CkPtStandAmountRate > 0 left join ndw.ndw_Shop shop on  s.ExtrSource = shop.ExtrSource and s.CompanyID=shop.CompanyID and
			s.ShopID = shop.ShopID left join ndw.ndw_global_dictionary dict on s.ExtrSource = dict.ExtrSource and s.CompanyID=dict.CompanyID and 
			s.ParentAreaID = dict.dictValue left join nods.nods_erp_SD_Mat_Kind kind on s.ExtrSource = kind.ExtrSource and 
			s.KindID = kind.KindID left join nods.nods_erp_SD_Mat_Model model on s.ExtrSource = model.ExtrSource and 
			s.ModelID = model.ModelID left join nods.nods_erp_SD_Mat_Series series on  s.ExtrSource = series.ExtrSource and 
			s.SeriesID = series.SeriesID left join nods.nods_erp_SD_Mat_Item item on s.ExtrSource = item.ExtrSource and 
			s.ItemID = item.ItemID
		WHERE
                        s.ExtrSource = '{UserInfo.UserExtrSource}' and s.CompanyID= '{UserInfo.UserCompanyId}'  and
						   s.CkSKCQty > 0 and
						  s.Yearv={Yearv} and s.Monthv={Monthv}  

{(string.IsNullOrWhiteSpace(ParentAreaID) ? "" : $" and s.ParentAreaID='{ParentAreaID}' ")}
{(string.IsNullOrWhiteSpace(KindID) ? "" : $" and s.KindID='{KindID}' ")}
{(string.IsNullOrWhiteSpace(SeriesID) ? "" : $" and s.SeriesID='{SeriesID}' ")}
{(string.IsNullOrWhiteSpace(MaterialProperty) ? "" : $" and s.MaterialProperty='{MaterialProperty}' ")}
{(string.IsNullOrWhiteSpace(ModelID) ? "" : $" and s.ModelID='{ModelID}' ")}
{(string.IsNullOrWhiteSpace(ItemID) ? "" : $" and s.ItemID='{ItemID}' ")}
{(string.IsNullOrWhiteSpace(DataFlag) ? "" : $" and s.DataFlag='{DataFlag}' ")}
{(string.IsNullOrWhiteSpace(ShopIDs)
                        ? ""
                        : $" and  shop.shopid in {ShopIDs}  ")} 
		
	) as p left join ngoods.ngoods_PL_Item_SkcForPCDetail sp on
    sp.ExtrSource = '{UserInfo.UserExtrSource}' and sp.CompanyID= '{UserInfo.UserCompanyId}'  and
	p.ParentAreaID = sp.ParentAreaID
	and p.Yearv = sp.Yearv
	and p.Monthv = sp.Monthv
	and p.ParentAreaID = sp.ParentAreaID
	and p.KindID = sp.KindID
	and p.SeriesID = sp.SeriesID
	and p.MaterialProperty = sp.MaterialProperty
	and p.ModelID = sp.ModelID
	and p.ItemID = sp.ItemID
    and p.dataflag = sp.dataflag
	and p.PriceColorName = to_number(sp.PriceColorName)
	and p.Weekv = sp.Weekv
	and p.ShopId = sp.ShopId
	and sp.PriceOrColor = 0
group by
	p.Yearv,
	p.Monthv,
	p.ParentAreaID,
	p.ShopName,
	p.KindName,
	p.SeriesName,
	p.MaterialProperty,
	p.ModelName,
	p.ItemName,
	p.Weekv,
	p.CkSKCQty,
	p.PriceColorName,
	p.CkPtStandAmountRate,
	p.SeriesID,
	p.ItemID,
    p.dataflag,
	p.ShopCode,
	p.ShopID,
	p.KindID,
	p.ModelID
                    ";
                    if (SeriesID == String.Empty || SeriesID == "000005")
                    {
                        countSql += $@"

UNION ALL --查询推品
SELECT
	p.Yearv,
	p.Monthv,
	p.ParentAreaID,
	p.ShopName,
	kind.KindName,
	series.SeriesName,
	p.MaterialProperty,
	model.ModelName,
	p.Weekv,
	item.ItemName,
	p.CkSKCQty,
	0 as TotalQty,
	p.PriceColorName,
	1.0 as CkPtStandAmountRate,
	p.SeriesID,
	p.ItemID,
	p.ShopCode,
	p.ShopID,
	p.KindID,
	p.ModelID,
	p.DeepQty,
	0 as CalcQty,
	0 as RoundQty,
	0.0 as DiffValue,
	0 as AddQty,
	p.CkSKCQty as PriceColorSKCQty,
    0 as DataFlag
from
	(
		select
			yv.weekv,
			pt.Yearv,
			pt.Monthv,
			pt.ParentAreaID,
			pt.ShopID,
			s.ShopCode,
			s.ShopName,
			pt.KindID,
			pt.SeriesID,
			pt.MaterialProperty,
			pt.ModelID,
			pt.ItemID,
			ROUND( CAST( dictvalue * updaterate * developrate as NUMERIC )) as CkSKCQty,
			cof.Price as PriceColorName,
			pt.CKMoveDeepQty as DeepQty
		from
			ngoods.ngoods_PL_ShopMSaleTrend pt,
			(
				select
					Yearv,
					Weekv
				from
					ngoods.ngoods_PL_Bas_SalePlan
				where
    ExtrSource = '{UserInfo.UserExtrSource}' and CompanyID= '{UserInfo.UserCompanyId}'  and
					Monthv = {Monthv}  
				group by
					Yearv,
					Weekv
			) yv,
			ndw.ndw_Shop s,
			(
				select
					c.dictName,
					c.dictValue
				from
					ndw.ndw_global_parent_dictionary p,
					ndw.ndw_global_dictionary c
				where
 p.ExtrSource = c.ExtrSource and p.CompanyID=c.CompanyID and
    p.ExtrSource = '{UserInfo.UserExtrSource}' and p.CompanyID= '{UserInfo.UserCompanyId}'  and
					p.dictName = c.parentDictValue
					and p.dictName = '推台SKC数'
			) d,
			ngoods.ngoods_PL_Bas_TableConf cof
		where
    pt.ExtrSource = '{UserInfo.UserExtrSource}' and pt.CompanyID= '{UserInfo.UserCompanyId}'  and
			ROUND( CAST( dictvalue * updaterate * developrate as NUMERIC )) > 0
			and pt.yearv = yv.Yearv
			and pt.ShopID = s.ShopID
            and pt.ExtrSource = s.ExtrSource and pt.CompanyID=s.CompanyID
			and s.ShopLevelCode = d.dictName
			and pt.ParentAreaID = cof.ParentAreaID
			and pt.Yearv = cof.Yearv
			and yv.weekv = cof.Weekv
			and pt.ItemID = cof.ItemID
            and pt.ExtrSource = cof.ExtrSource and pt.CompanyID=cof.CompanyID
            and pt.SeriesID = '000005'
            and pt.MaterialProperty = '流行款'
			and pt.Yearv={Yearv} and pt.Monthv={Monthv}  
       
{(string.IsNullOrWhiteSpace(ParentAreaID) ? "" : $" and pt.ParentAreaID='{ParentAreaID}' ")}
{(string.IsNullOrWhiteSpace(KindID) ? "" : $" and pt.KindID='{KindID}' ")}
{(string.IsNullOrWhiteSpace(MaterialProperty) ? "" : $" and pt.MaterialProperty='{MaterialProperty}' ")}
{(string.IsNullOrWhiteSpace(ModelID) ? "" : $" and pt.ModelID='{ModelID}' ")}
{(string.IsNullOrWhiteSpace(ItemID) ? "" : $" and pt.ItemID='{ItemID}' ")}
		{(string.IsNullOrWhiteSpace(ShopIDs)
                            ? ""
                            : $" and  s.shopid in {ShopIDs}  ")} 
	) as p left join ndw.ndw_global_dictionary dict on
	p.ParentAreaID = dict.dictValue left join nods.nods_erp_SD_Mat_Kind kind on
	p.KindID = kind.KindID left join nods.nods_erp_SD_Mat_Model model on
	p.ModelID = model.ModelID left join nods.nods_erp_SD_Mat_Series series on
	p.SeriesID = series.SeriesID left join nods.nods_erp_SD_Mat_Item item on
	p.ItemID = item.ItemID left
JOIN ngoods.ngoods_PL_Item_SkcForPCDetail sp on
    dict.ExtrSource = '{UserInfo.UserExtrSource}' and dict.CompanyID= '{UserInfo.UserCompanyId}'  and
kind.ExtrSource = '{UserInfo.UserExtrSource}' and
model.ExtrSource = '{UserInfo.UserExtrSource}' and
series.ExtrSource = '{UserInfo.UserExtrSource}' and
item.ExtrSource = '{UserInfo.UserExtrSource}' and
    sp.ExtrSource = '{UserInfo.UserExtrSource}' and sp.CompanyID= '{UserInfo.UserCompanyId}'  and
	p.ParentAreaID = sp.ParentAreaID
	and p.Yearv = sp.Yearv
	and p.Monthv = sp.Monthv
	and p.ParentAreaID = sp.ParentAreaID
	and p.KindID = sp.KindID
	and p.SeriesID = sp.SeriesID
	and p.MaterialProperty = sp.MaterialProperty
	and p.ModelID = sp.ModelID
	and p.ItemID = sp.ItemID
	and sp.DataFlag = 0
	and p.PriceColorName = to_number(sp.PriceColorName)
	and p.Weekv = sp.Weekv
	and p.ShopId = sp.ShopId
	and sp.PriceOrColor = 0
	

";
                    }
                    countSql += ") as t";

                    #endregion 计算时获取行数
                }
                else
                {
                    #region 查询时获取行数

                    countSql = $@"

select count(1) as count from(
SELECT
			sp.yearv,
			sp.monthv,
			sp.parentareaid,
			shop.shopname AS ShopName,
			kind.kindname AS KindName,
			series.seriesname AS SeriesName,
			sp.materialproperty,
			model.modelname AS ModelName,
			sp.weekv,
			item.itemname AS ItemName,
			sp.ckskcqty,
			sp.PriceColorName,
			sp.CkPtStandAmountRate,
			sp.seriesid,
			sp.itemid,
			shop.shopcode AS ShopCode,
			sp.shopid,
			sp.kindid,
			sp.modelid,
            sp.DeepQty as DeepQty,
			sp.totalqty,
			sp.calcqty,
			sp.roundqty,
			sp.diffvalue,
			sp.addqty,
			sp.pricecolorskcqty
		FROM
			ngoods.ngoods_pl_item_skcforpcDetail sp
		LEFT JOIN ndw.ndw_shop shop ON   sp.ExtrSource = shop.ExtrSource and sp.CompanyID=shop.CompanyID and
			sp.shopid = shop.shopid
		LEFT JOIN ndw.ndw_global_dictionary dict ON   sp.ExtrSource = dict.ExtrSource and sp.CompanyID=dict.CompanyID and
			sp.parentareaid = dict.dictvalue
		LEFT JOIN nods.nods_erp_sd_mat_kind kind ON  sp.ExtrSource = kind.ExtrSource and
			sp.kindid = kind.kindid
		LEFT JOIN nods.nods_erp_sd_mat_model model ON  sp.ExtrSource = model.ExtrSource and
			sp.modelid = model.modelid
		LEFT JOIN nods.nods_erp_sd_mat_series series ON sp.ExtrSource = series.ExtrSource and
			sp.seriesid = series.seriesid
		LEFT JOIN nods.nods_erp_sd_mat_item item ON  sp.ExtrSource = item.ExtrSource and
			sp.itemid = item.itemid
		WHERE
    sp.ExtrSource = '{UserInfo.UserExtrSource}' and sp.CompanyID= '{UserInfo.UserCompanyId}'  and
		sp.priceorcolor = 0 and
			sp.ckskcqty > 0 and
			
			
			 sp.Yearv={Yearv} and sp.Monthv={Monthv}  

{(string.IsNullOrWhiteSpace(ParentAreaID) ? "" : $" and sp.ParentAreaID = '{ParentAreaID}' ")}
{ (string.IsNullOrWhiteSpace(KindID) ? "" : $" and sp.KindID='{KindID}' ")}
                    { (string.IsNullOrWhiteSpace(SeriesID) ? "" : $" and sp.SeriesID='{SeriesID}' ")}
                    { (string.IsNullOrWhiteSpace(MaterialProperty) ? "" : $" and sp.MaterialProperty='{MaterialProperty}' ")}
                    { (string.IsNullOrWhiteSpace(ModelID) ? "" : $" and sp.ModelID='{ModelID}' ")}
                    { (string.IsNullOrWhiteSpace(ItemID) ? "" : $" and sp.ItemID='{ItemID}' ")}
                    {(string.IsNullOrWhiteSpace(DataFlag) ? "" : $" and sp.DataFlag='{DataFlag}' ")}

                    {
                        (string.IsNullOrWhiteSpace(ShopIDs)
                                               ? ""
                                               : $" and  shop.shopid in {ShopIDs}  ")}

) as t

";
                    #endregion 查询时获取行数
                }

                var cmd = Helper.GetSqlStringCommand(countSql);

                var count = Convert.ToInt32(Helper.ExecuteScalar(cmd));
                return count;
            }
            catch (Exception ex)
            {
                throw (ex);
            }
        }


        #endregion 返回行数

        #region 计算辅助方法

        #region 全部计算

        public IList<PLItemPriceZoneSkcModel.ListModel> ComputeAll(PaperData<IGrouping<string, PLItemPriceZoneSkcModel.ListModel>> result)
        {
            //计算SKC合计,TotalQty
            ComputeTotalQty(result.Items);
            //计算skc数 和 计算结果,差额
            ComputeCalcQty(result.Items);
            //计算增加/扣减
            result.Items = ComputeAddQty(result.Items);
            //计算skc数
            var list = ComputePirceColorSKCQty(result.Items);
            return list;
        }

        #endregion 全部计算

        #region 计算SKC合计
        /// <summary>
        /// 需求:2、第一周SKC合计＝第一周-短T+0;第二周SKC合计=第二周-短T+第一周-短Ｔ;如表格:20+30=50;第三，第四以此类推(前几周的合计);
        /// </summary>
        /// <param name="items"></param>
        public void ComputeTotalQty(IList<IGrouping<string, PLItemPriceZoneSkcModel.ListModel>> items)
        {
            //更新每周的SKC合计
            foreach (var row in items)
            {
                int count = 0;
                //上一周的SKC合计,有可能不是连续的周
                int previousTotalQty = 0;
                foreach (var item in row)
                {
                    if (0 == count)
                    {
                        //获取第一周
                        item.TotalQty = item.CkSKCQty;
                    }
                    else
                    {

                        item.TotalQty = item.CkSKCQty + previousTotalQty;
                    }
                    previousTotalQty = item.TotalQty;
                    count++;
                }
            }


        }

        #endregion 计算SKC合计

        #region 计算skc数 和 计算结果,差额

        /// <summary>
        /// 3、计算skc数=skc合计*价格带占比 ；保留两位小数 ;
        /// 4、计算结果=计算skc数四舍五入，取整；
        /// 5、差额=计算结果－计算skc数；
        /// </summary>
        /// <param name="items"></param>
        public void ComputeCalcQty(IList<IGrouping<string, PLItemPriceZoneSkcModel.ListModel>> items)
        {
            //更新计算skc数 和 计算结果,差额
            for (int i = 0; i < items.Count; i++)
            {
                items[i].ToList().ForEach(
                       (p) =>
                       {
                           p.CalcQty = Convert.ToDecimal(MathHelper.Rounding(Convert.ToDouble(p.TotalQty * p.CkPtStandAmountRate), 2));
                           p.RoundQty = int.Parse(MathHelper.Rounding(Convert.ToDouble(p.CalcQty), 0).ToString());
                           p.DiffValue = p.RoundQty - p.CalcQty;
                       }
                    );
            }
        }


        #endregion 计算skc数 和 计算结果,差额

        #region 计算增加/扣减


        /// <summary>
        /// 增加/扣减 计算
        /// </summary>
        /// <param name="items"></param>
        public IList<IGrouping<string, PLItemPriceZoneSkcModel.ListModel>> ComputeAddQty(IList<IGrouping<string, PLItemPriceZoneSkcModel.ListModel>> list)
        {
            //重新分组,以方便计算,原来是按价格带分组.修改为按周分组.每周包含所有价格带
            var newList = list.SelectMany(p => p).ToList();
            var items = newList.GroupBy(
                                        d =>
                                            d.Yearv + "_" + d.Monthv + "_" + d.ParentAreaID + "_" + d.ShopID + "_" +
                                            d.KindID + "_" + d.MaterialProperty + "_" + d.ModelID + "_" + d.SeriesID + "_" +
                                            d.ItemID + "_" + d.DataFlag + "_" + d.CkSKCQty + "_" + d.Weekv
                                            ).ToList();
            //增加/扣减 计算
            for (int i = 0; i < items.Count; i++)
            {
                //判断如果价格带为0,就跳过计算

                //判断以周为单位的计算结果RoundQty合计 和 SKC合计TotalQty 的大小关系
                var result = items[i].Sum(p => p.RoundQty) - items[i].FirstOrDefault().TotalQty;
                //先清空已有的AddQty
                items[i].ToList().ForEach(p => p.AddQty = 0);
                //等于
                if (result == 0)
                {
                    //不做操作,默认已经是0了
                }
                //大于
                else if (result > 0)
                {
                    //排序
                    //先按差额从大到小排序,
                    //再按价格带从大到小排序
                    var itemsOrdered = items[i].OrderByDescending(p => p.DiffValue).ThenByDescending(p => p.PriceColorName).ToList();
                    for (int index = 0; index < result; index++)
                    {
                        if (itemsOrdered.Count - 1 < index)
                        {
                            break;
                        }
                        if (0 == itemsOrdered[index].PriceColorName)
                        {
                            continue;
                        }
                        //为防止出来skc负数,要在计算结果合计>0时,判断当前计算结果和前几周skc合计是否相等
                        //只有当前周的当前价格带计算结果大于前几周skc合计,才允许扣减
                        var first =
                            items[i].ToList()
                                .Where(p => p.PriceColorName == itemsOrdered[index].PriceColorName)
                                .FirstOrDefault();
                        var PreviousPriceColorSKC = newList.Where(
                            p => p.Yearv == first.Yearv &&
                                 p.Monthv == first.Monthv &&
                                 p.ParentAreaID == first.ParentAreaID &&
                                 p.ShopID == first.ShopID &&
                                 p.KindID == first.KindID &&
                                 p.SeriesID == first.SeriesID &&
                                 p.MaterialProperty == first.MaterialProperty &&
                                 p.ModelID == first.ModelID &&
                                 p.Weekv == first.Weekv &&
                                 p.ItemID == first.ItemID &&
                                 p.DataFlag == first.DataFlag &&
                                 p.PriceColorName == first.PriceColorName

                        ).Sum(p => p.PriceColorSKCQty);
                        //如果大于
                        if (first.RoundQty > PreviousPriceColorSKC)
                        {
                            items[i].ToList().Where(p => p.PriceColorName == itemsOrdered[index].PriceColorName).FirstOrDefault().AddQty = -1;
                        }
                    }
                }
                //小于
                else
                {
                    //排序
                    //先按差额从小到大排序,
                    //再按价格带从小到大排序
                    var itemsOrdered = items[i].OrderBy(p => p.DiffValue).ThenBy(p => p.PriceColorName).ToList();
                    for (int index = 0; index < Math.Abs(result); index++)
                    {
                        if (itemsOrdered.Count - 1 < index)
                        {
                            break;
                        }
                        if (0 == itemsOrdered[index].PriceColorName)
                        {
                            continue;
                        }
                        items[i].ToList().Where(p => p.PriceColorName == itemsOrdered[index].PriceColorName).FirstOrDefault().AddQty = 1;
                    }
                }
            }
            //计算完毕,还原分组.方便下次运算
            var tempList2 = items.SelectMany(p => p).ToList();
            var itemsGrouped = tempList2.GroupBy(
                                        d =>
                                            d.Yearv + "_" + d.Monthv + "_" + d.ParentAreaID + "_" + d.ShopID + "_" +
                                            d.KindID + "_" + d.MaterialProperty + "_" + d.ModelID + "_" + d.SeriesID + "_" +
                                             d.ItemID + "_" + d.DataFlag + "_" + d.PriceColorName
                                            ).ToList();
            return itemsGrouped;
        }


        #endregion 计算增加/扣减

        #region 计算skc数



        /// <summary>
        /// 9、计算PriceColorSKCQty SKC数
        /// </summary>
        /// <param name="items"></param>
        public IList<PLItemPriceZoneSkcModel.ListModel> ComputePirceColorSKCQty(IList<IGrouping<string, PLItemPriceZoneSkcModel.ListModel>> items)
        {
            //items为按价格带分组.每个分组中是此小品类中单个价格带一个月的信息
            //更新计算skc数

            //重新分组,以方便计算,原来是按价格带分组.修改为按周分组.每周包含所有价格带
            var newList = items.SelectMany(p => p).ToList();
            var Newitems = newList.GroupBy(
                            d => new
                            {
                                d.Yearv,
                                d.Monthv,
                                d.ParentAreaID,
                                d.ShopID,
                                d.KindID,
                                d.MaterialProperty,
                                d.ModelID,
                                d.SeriesID,
                                d.ItemID,
                                d.DataFlag
                            }, (ItemName, WeekGroup) => new
                            {
                                ItemName,
                                WeekGroup = WeekGroup.GroupBy(e => e.Weekv, (Weekv, PriceGroup) => new
                                {
                                    Weekv,
                                    PriceGroup
                                })
                            });
            foreach (var item in Newitems)
            {
                //前N周单价格带SKC合计
                Dictionary<string, int> previousTotalQtySumDict = new Dictionary<string, int>();
                foreach (var price in item.WeekGroup.First().PriceGroup)
                {
                    previousTotalQtySumDict.Add(price.PriceColorName.ToString(), 0);
                }
                //遍历小品类下边的所有周
                foreach (var week in item.WeekGroup)
                {
                    //遍历周里边的所有价格带
                    foreach (var price in week.PriceGroup)
                    {
                        //为防止skc数出现负数,计算时先判断,
                        //当前价格带的计算结果+增加/扣减-前N周skc的合计 > 0
                        //如果出现负数.就重置为0
                        if (price.RoundQty + price.AddQty - previousTotalQtySumDict[price.PriceColorName.ToString()] > 0)
                        {
                            price.PriceColorSKCQty = price.RoundQty + price.AddQty - previousTotalQtySumDict[price.PriceColorName.ToString()];
                        }
                        else
                        {
                            price.PriceColorSKCQty = 0;
                        }
                    }

                    #region 校正skc

                    //校正skc
                    //判断以周为单位的计算结果PriceColorSKCQty合计 和 CkSKCQty 的大小关系
                    var result = week.PriceGroup.Sum(p => p.PriceColorSKCQty) - week.PriceGroup.FirstOrDefault().CkSKCQty;

                    //大于
                    if (result > 0)
                    {
                        //排序
                        //先按差额从大到小排序,
                        //再按价格带从大到小排序
                        var itemsOrdered = week.PriceGroup.Where(p => p.PriceColorSKCQty > 0)
                                                   .OrderByDescending(p => p.DiffValue)
                                                   .ThenByDescending(p => p.PriceColorName).ToList();
                        for (int index = 0; index < result; index++)
                        {
                            if (itemsOrdered.Count - 1 < index)
                            {
                                break;
                            }
                            if (0 == itemsOrdered[index].PriceColorName)
                            {
                                continue;
                            }
                            if (week.PriceGroup.ToList().FirstOrDefault(p => p.PriceColorName == itemsOrdered[index].PriceColorName) != null)
                            {
                                week.PriceGroup.ToList().FirstOrDefault(p => p.PriceColorName == itemsOrdered[index].PriceColorName).PriceColorSKCQty += -1;
                            }
                        }
                    }
                    //小于
                    else if (result < 0)
                    {
                        //排序
                        //先按差额从小到大排序,
                        //再按价格带从小到大排序
                        var itemsOrdered = week.PriceGroup.OrderBy(p => p.DiffValue)
                                                   .ThenBy(p => p.PriceColorName).ToList();
                        for (int index = 0; index < Math.Abs(result); index++)
                        {
                            if (itemsOrdered.Count - 1 < index)
                            {
                                break;
                            }
                            if (0 == itemsOrdered[index].PriceColorName)
                            {
                                continue;
                            }
                            if (week.PriceGroup.ToList().FirstOrDefault(p => p.PriceColorName == itemsOrdered[index].PriceColorName) != null)
                            {
                                week.PriceGroup.ToList().FirstOrDefault(p => p.PriceColorName == itemsOrdered[index].PriceColorName).PriceColorSKCQty += 1;
                            }
                        }
                    }
                    #endregion 校正skc

                    //遍历周里边的所有价格带,把计算出的skc保存到previousTotalQtySumDict
                    foreach (var price in week.PriceGroup)
                    {
                        previousTotalQtySumDict[price.PriceColorName.ToString()] += price.PriceColorSKCQty;
                    }
                }
            }
            var list = new List<PLItemPriceZoneSkcModel.ListModel>();

            foreach (var item in Newitems)
            {
                //遍历小品类下边的所有周
                foreach (var week in item.WeekGroup)
                {
                    //遍历周里边的所有价格带
                    foreach (var price in week.PriceGroup)
                    {
                        list.Add(price);
                    }
                }
            }
            return list;
        }



        #endregion 计算skc数

        #endregion 计算辅助方法

        #region 修改小品类到价格带SKC数量

        [Obsolete]
        [JsonType]
        [MustLogin]
        [Description("修改小品类到价格带SKC数量")]
        [ReturnType(typeof(PaperData<IGrouping<string, PLItemPriceZoneSkcModel.ListModel>>))]
        public ApiResult Update([ParamDescription("商品区域")] PLItemPriceZoneSkcModel.ModifyModel param)
        {
            try
            {
                if (param == null) return Error("参数对象为空");
                InsertComputedList2DB(param.list);
                return Success(0);
            }
            catch (Exception ex)
            {
                return Error(ex.Message);
            }
        }

        #endregion 修改小品类到价格带SKC数量

        #region 删除所有的计算结果到数据库
        /// <summary>
        /// 保存所有的计算结果到数据库
        /// </summary>
        /// <param name="list"></param>
        public List<CommandInfo> DeleteComputedList2DB(long Yearv, long Monthv,
            string ParentAreaID, string KindID,
            string SeriesID, string MaterialProperty, string ModelID,
            string ItemID, string DataFlag, string ShopIDs)
        {

            var cmdList = new List<CommandInfo>();
            #region 删除

            var DeleteSql = $@"

DELETE
from
	ngoods.ngoods_PL_Item_SkcForPCDetail
where
    ExtrSource = '{UserInfo.UserExtrSource}' and CompanyID= '{UserInfo.UserCompanyId}'  and
	   Yearv = '{Yearv}'
	                and Monthv = '{Monthv}'
	                and PriceOrColor = '0' 
                    { (string.IsNullOrWhiteSpace(ParentAreaID) ? "" : $" and ParentAreaID = '{ParentAreaID}' ")}
                    { (string.IsNullOrWhiteSpace(KindID) ? "" : $" and KindID='{KindID}' ")}
                    { (string.IsNullOrWhiteSpace(SeriesID) ? "" : $" and SeriesID='{SeriesID}' ")}
                    { (string.IsNullOrWhiteSpace(MaterialProperty) ? "" : $" and MaterialProperty='{MaterialProperty}' ")}
                    { (string.IsNullOrWhiteSpace(ModelID) ? "" : $" and ModelID='{ModelID}' ")}
                    { (string.IsNullOrWhiteSpace(ItemID) ? "" : $" and ItemID='{ItemID}' ")}
                    {(string.IsNullOrWhiteSpace(DataFlag) ? "" : $" and DataFlag='{DataFlag}' ")}

					                    { (string.IsNullOrWhiteSpace(ShopIDs) ? "" : $@" 	and ShopID In {ShopIDs} ")}
                    ";

            #endregion 删除
            var cmdSave = new CommandInfo()
            {
                CommandText = DeleteSql
            };
            cmdList.Add(cmdSave);
            return cmdList;
        }
        #endregion 删除所有的计算结果到数据库

        #region 保存所有的计算结果到数据库
        /// <summary>
        /// 保存所有的计算结果到数据库
        /// </summary>
        /// <param name="list"></param>
        public void InsertComputedList2DB(IList<PLItemPriceZoneSkcModel.ListModel> list)
        {
            if (list != null && list.Count() > 0)
            {
                var cmdList = new List<CommandInfo>();

                var dtInsert = DataTableHelper.LinqToDataTable(list.Select(
                    p => new
                    {
                        p.Yearv,
                        p.Monthv,
                        p.Weekv,
                        p.ParentAreaID,
                        p.ShopID,
                        p.PriceOrColor,
                        p.KindID,
                        p.SeriesID,
                        p.MaterialProperty,
                        p.ModelID,
                        p.ItemID,
                        p.DataFlag,
                        p.DeepQty,
                        p.CkSKCQty,
                        p.TotalQty,
                        p.PriceColorName,
                        p.CkPtStandAmountRate,
                        p.CalcQty,
                        p.RoundQty,
                        p.DiffValue,
                        p.AddQty,
                        p.PriceColorSKCQty,
                        p.CompanyID,
                        p.ExtrSource,
                        p.MofifyDTM,
                        p.CheckState,
                    }
                    ).ToList());
                Helper.ExecuteSqlTran(cmdList, dtInsert, "ngoods.ngoods_PL_Item_SkcForPCDetail");
            }
        }
        #endregion 保存所有的计算结果到数据库

        #region 查询并计算含推品的数据

        public void InsertTuiPin2DB(long Yearv, long Monthv,
            [ParamDescription("商品区域")] string ParentAreaID = "",
            [ParamDescription("类别")]string KindID = "",
            [ParamDescription("系列")]string SeriesID = "",
            [ParamDescription("商品层")]string MaterialProperty = "",
            [ParamDescription("品类")]string ModelID = "",
            [ParamDescription("小品类")]string ItemID = "",
            [ParamDescription("款")]string DataFlag = "",
            [ParamDescription("店铺shopid")]string ShopIDs = "",
            [ParamDescription("页码")] int Page = 1,
            [ParamDescription("页数")] int Size = 10)
        {
            try
            {
                //推品都是共性款,如果只查询差异款,直接返回.不再执行接下来的操作
                if (string.IsNullOrWhiteSpace(DataFlag) && DataFlag == "1")
                {
                    return;
                }
                //查询推品
                string sqlTuiPin = $@"

SELECT
	p.Yearv,
	p.Monthv,
	p.ParentAreaID,
	p.ShopName,
	kind.KindName,
	series.SeriesName,
	p.MaterialProperty,
	model.ModelName,
	p.Weekv,
	item.ItemName,
	p.CkSKCQty,
	0 as TotalQty,
	cast(
				p.PriceColorName as decimal(
					8,
					1
				)
			) as PriceColorName,
	1.0 as CkPtStandAmountRate,
	p.SeriesID,
	p.ItemID,
	p.ShopCode,
	p.ShopID,
	p.KindID,
	p.ModelID,
	p.DeepQty,
	0.0 as CalcQty,
	0 as RoundQty,
	0.0 as DiffValue,
	0 as AddQty,
	p.CkSKCQty as PriceColorSKCQty,
    0 as DataFlag
from
	(
		select
			yv.weekv,
			pt.Yearv,
			pt.Monthv,
			pt.ParentAreaID,
			pt.ShopID,
			s.ShopCode,
			s.ShopName,
			pt.KindID,
			pt.SeriesID,
			pt.MaterialProperty,
			pt.ModelID,
			pt.ItemID,
			CAST(ROUND( CAST( dictvalue * updaterate * developrate as NUMERIC )) as BIGINT) as CkSKCQty,
			cof.Price as PriceColorName,
			pt.CKMoveDeepQty as DeepQty
		from
			ngoods.ngoods_PL_ShopMSaleTrend pt,
			(
				select
					Yearv,
					Weekv
				from
					ngoods.ngoods_PL_Bas_SalePlan
				where
    ExtrSource = '{UserInfo.UserExtrSource}' and CompanyID= '{UserInfo.UserCompanyId}'  and
					Monthv = {Monthv}  
				group by
					Yearv,
					Weekv
			) yv,
			ndw.ndw_Shop s,
			(
				select
					c.dictName,
					c.dictValue
				from
					ndw.ndw_global_parent_dictionary p,
					ndw.ndw_global_dictionary c
				where
    p.ExtrSource = c.ExtrSource and p.CompanyID= c.CompanyID  and
    p.ExtrSource = '{UserInfo.UserExtrSource}' and p.CompanyID= '{UserInfo.UserCompanyId}'  and
					p.dictName = c.parentDictValue
					and p.dictName = '推台SKC数'
			) d,
			ngoods.ngoods_PL_Bas_TableConf cof
		where
    pt.ExtrSource = '{UserInfo.UserExtrSource}' and pt.CompanyID= '{UserInfo.UserCompanyId}'  and
			pt.yearv = yv.Yearv
			and pt.ShopID = s.ShopID
            and pt.ExtrSource = s.ExtrSource and pt.CompanyID= s.CompanyID 
			and s.ShopLevelCode = d.dictName
			and pt.ParentAreaID = cof.ParentAreaID
            and pt.ExtrSource = cof.ExtrSource and pt.CompanyID= cof.CompanyID
			and pt.Yearv = cof.Yearv
			and yv.weekv = cof.Weekv
			and pt.ItemID = cof.ItemID
			and pt.SeriesID = '000005'
            and pt.MaterialProperty = '流行款'
            and pt.Yearv={Yearv} and pt.Monthv={Monthv}  
       
{(string.IsNullOrWhiteSpace(ParentAreaID) ? "" : $" and pt.ParentAreaID='{ParentAreaID}' ")}
{(string.IsNullOrWhiteSpace(KindID) ? "" : $" and pt.KindID='{KindID}' ")}
{(string.IsNullOrWhiteSpace(MaterialProperty) ? "" : $" and pt.MaterialProperty='{MaterialProperty}' ")}
{(string.IsNullOrWhiteSpace(ModelID) ? "" : $" and pt.ModelID='{ModelID}' ")}
{(string.IsNullOrWhiteSpace(ItemID) ? "" : $" and pt.ItemID='{ItemID}' ")}
		{(string.IsNullOrWhiteSpace(ShopIDs)
                            ? ""
                            : $" and s.shopid in {ShopIDs} ")} 
	) as p left join ndw.ndw_global_dictionary dict on     dict.ExtrSource = '{UserInfo.UserExtrSource}' and dict.CompanyID= '{UserInfo.UserCompanyId}'  and
	p.ParentAreaID = dict.dictValue left join nods.nods_erp_SD_Mat_Kind kind on     kind.ExtrSource = '{UserInfo.UserExtrSource}' and
	p.KindID = kind.KindID left join nods.nods_erp_SD_Mat_Model model on   model.ExtrSource = '{UserInfo.UserExtrSource}' and
	p.ModelID = model.ModelID left join nods.nods_erp_SD_Mat_Series series on series.ExtrSource = '{UserInfo.UserExtrSource}' and
	p.SeriesID = series.SeriesID left join nods.nods_erp_SD_Mat_Item item on   item.ExtrSource = '{UserInfo.UserExtrSource}' and
	p.ItemID = item.ItemID left
JOIN ngoods.ngoods_PL_Item_SkcForPCDetail sp on
    sp.ExtrSource = '{UserInfo.UserExtrSource}' and sp.CompanyID= '{UserInfo.UserCompanyId}'  and
	p.ParentAreaID = sp.ParentAreaID
	and p.Yearv = sp.Yearv
	and p.Monthv = sp.Monthv
	and p.ParentAreaID = sp.ParentAreaID
	and p.KindID = sp.KindID
	and p.SeriesID = sp.SeriesID
	and p.MaterialProperty = sp.MaterialProperty
	and p.ModelID = sp.ModelID
	and p.ItemID = sp.ItemID
	and sp.DataFlag = 1
	and p.PriceColorName = to_number(sp.PriceColorName)
	and p.Weekv = sp.Weekv
	and p.ShopId = sp.ShopId
	and sp.PriceOrColor = 0

";
                var cmd = Helper.GetSqlStringCommand(sqlTuiPin);
                var dt = Helper.ExecuteDataTable(cmd);
                var list = ModelConvertHelper<PLItemPriceZoneSkcModel.ListModel>.ConvertToModel(dt);
                List<CommandInfo> cmdEmpty = new List<CommandInfo>();
                ExecuteSkcCalc(cmdEmpty, list);
            }
            catch (Exception e)
            {
                throw (e);
            }
        }

        void ExecuteSkcCalc(List<CommandInfo> deleteCmd, IList<PLItemPriceZoneSkcModel.ListModel> list)
        {
            var dtInsert = DataTableHelper.LinqToDataTable(list.Select(
               p => new
               {
                   p.Yearv,
                   p.Monthv,
                   p.Weekv,
                   p.ParentAreaID,
                   p.ShopID,
                   p.PriceOrColor,
                   p.KindID,
                   p.SeriesID,
                   p.MaterialProperty,
                   p.ModelID,
                   p.ItemID,
                   p.DataFlag,
                   p.DeepQty,
                   p.CkSKCQty,
                   p.TotalQty,
                   p.PriceColorName,
                   p.CkPtStandAmountRate,
                   p.CalcQty,
                   p.RoundQty,
                   p.DiffValue,
                   p.AddQty,
                   p.PriceColorSKCQty,
                   p.CompanyID,
                   p.ExtrSource,
                   p.MofifyDTM,
                   p.Checker,
               }
               ).ToList());
            Helper.ExecuteSqlTran(deleteCmd, dtInsert, "ngoods.ngoods_PL_Item_SkcForPCDetail");
        }

        #endregion 查询并计算含推品的数据
    }

}
